from flask import Blueprint, render_template, jsonify, request, send_file
from asset_situation_analysis import AssetSituationAnalyzer
import pandas as pd
import numpy as np
from io import BytesIO
from datetime import datetime
from decimal import Decimal
import os
from werkzeug.utils import secure_filename
from pathlib import Path

# 创建Blueprint
asset_situation_bp = Blueprint('asset_situation', __name__)

analyzer = AssetSituationAnalyzer()
# 初始化数据跟路径
analyzer.get_data_dir()
def get_resource_path(relative_path):
    return analyzer.get_base_path(relative_path)
@asset_situation_bp.route('/asset-situation')
def asset_situation():
    """渲染资产筛选页面"""
    print('访问资产筛选页面')
    return render_template('pages/asset_situation.html', active_page='asset_situation')

@asset_situation_bp.route('/check-data', methods=['GET'])
def check_data():
    """检查是否存在资产关系数据，同时返回亲属关系数据"""
    try:
        return jsonify(analyzer.check_data('银行流水数据库.xlsx', 'source'))

    except Exception as e:
        print(f"[DEBUG] 发生错误: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({
            'exists': False,
            'message': f'检查数据时出错: {str(e)}',
            'folders': []
        })



@asset_situation_bp.route('/upload-file', methods=['POST'])
def upload_file():
    """
    实现上传文件到uploads文件夹下，每次上传文件，在文件后方添加时间戳
    """
    if 'file' not in request.files:
        return jsonify({'success': False, 'message': '未找到文件上传字段'})
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({'success': False, 'message': '未选择文件'})
    
    if file:
        # 确保uploads文件夹存在
        upload_folder = 'uploads'
        if not os.path.exists(upload_folder):
            os.makedirs(upload_folder)
        
        # 分离文件名和扩展名
        name, ext = os.path.splitext(file.filename)
        print(f"name:{name}")
        print(f"ext:{ext}")
        # 验证文件为excel文件
        allowed_extensions = ['.xlsx', '.xls']
        if ext.lower() not in allowed_extensions:
            return jsonify({
                'success': False,
                'message': '仅支持上传 Excel 文件（.xlsx 或 .xls）'
            })
        # 添加时间戳
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
        new_filename = f"{name}_{timestamp}{ext}"
        file_path = os.path.join(upload_folder, new_filename)
        
        try:
            file.save(file_path)
            return jsonify({
                'success': True,
                'message': '文件上传成功',
                'file_path': file_path
            })
        except Exception as e:
            print(f"[DEBUG] 上传文件时出错: {str(e)}")
            return jsonify({
                'success': False,
                'message': f'文件上传失败: {str(e)}'
            })

@asset_situation_bp.route('/get-result', methods=['GET'])
def get_result():
    # 被审查人
    subdirectories = request.args.getlist('folders')
    # 审查亲属
    selected_relatives = request.args.get('relatives')
    check_file_type = request.args.get('check_file_type')
    filepath = request.args.get('filepath')
    is_excel = request.args.get('is_excel')
    chazhi = request.args.get('chazhi')
    chazhiRate = request.args.get('chazhi_rate')

    try:
        import json
        selected_relatives = json.loads(selected_relatives) if selected_relatives else {}
    except json.JSONDecodeError:
        print("selected_relatives 不是有效的 JSON 字符串")
        selected_relatives = {}

    # 查找资产
    try:
        # 初始化资源路径
        analyzer.get_source_dir('source')
        # 获取资源路径下的所有子目录名称
        if not subdirectories:
            subdirectories = analyzer.get_subdirectories('source')

        # 定义常量文件名
        ASSET_RELATION_FILE = '资产关系.xlsx'
        STAFF_FILE = '特定关系人.xlsx'
        BANK_DATA_FILE = '银行流水数据库.xlsx'

        # 加载资产关系数据
        company_dfs = []
        for item in subdirectories:
            file_path = analyzer.source_dir / item / ASSET_RELATION_FILE
            if not file_path.exists():
                return jsonify({
                    'success': False,
                    'message': f'{item}的资产关系.xlsx文件不存在'
                })
            df = analyzer.read_excel_files(analyzer.source_dir / item, ASSET_RELATION_FILE, [], 1)
            if selected_relatives and item in selected_relatives:
                df = df[df['所属人员'].isin(selected_relatives[item])]
            if not df.empty and df is not False:
                company_dfs.append(df)

        if not company_dfs:
            return jsonify({
                'success': False,
                'message': '没有资产数据'
            })

        combined_df = pd.concat(company_dfs, ignore_index=True)
        # 格式化所有datetime列
        for col in combined_df.select_dtypes(include=['datetime64']).columns:
            combined_df[col] = combined_df[col].dt.strftime('%Y-%m-%d %H:%M:%S')

        company = combined_df.to_dict('records')

        # 处理数据类型转换
        def process_record(record):
            for key, value in record.items():
                if pd.isna(value):
                    record[key] = None
                elif isinstance(value, (pd.Timestamp, pd.DatetimeTZDtype)):
                    record[key] = value.isoformat()
                elif isinstance(value, (np.int64, np.float64)):
                    record[key] = float(value) if isinstance(value, np.float64) else int(value)
            return record

        company = [process_record(record) for record in company]

        # 处理自定义文件路径
        yhFile = None
        dir_path = None
        if check_file_type == "2":
            if not filepath:
                return jsonify({
                    'success': False,
                    'message': '自定义文件路径为空'
                })
            filepath_parts = filepath.split('\\')
            if len(filepath_parts) < 2:
                return jsonify({
                    'success': False,
                    'message': '自定义文件路径格式错误'
                })
            yhFile = filepath_parts[1]
            dir_path = Path(filepath_parts[0]).absolute()
            dir_path.mkdir(parents=True, exist_ok=True)

        # 遍历资产数据，关联特定关系人和银行流水数据
        for zichanItem in company:
            print(f"{zichanItem.get('服务商', '')}")
            filtered_values = [
                value for value in [
                    zichanItem.get('服务商', ''),
                    zichanItem.get('开发商', ''),
                    zichanItem.get('销售商', ''),
                    zichanItem.get('其他', '')
                ] if value and not pd.isna(value)
            ]
            if not filtered_values:
                continue
            
            qinshuFile = '亲属关系.xlsx'
            for item in subdirectories:
                # 处理特定关系人数据
                staff_file_path = analyzer.source_dir / item / STAFF_FILE
                if not staff_file_path.exists():
                    return jsonify({
                        'success': False,
                        'message': f'{item}的特定关系人.xlsx文件不存在'
                    })
                df1 = analyzer.read_excel_files(analyzer.source_dir / item, STAFF_FILE, [], 0)
                if df1.empty:
                    continue
                # 查询亲属关系表
                qinshu_file_path = analyzer.source_dir / item / qinshuFile
                if not qinshu_file_path.exists():
                    return jsonify({
                        'success': False,
                        'message': f'{item}的特定关系人.xlsx文件不存在'
                    })
                qishu_df = analyzer.read_excel_files(analyzer.source_dir / item, qinshuFile, [], 1)
                

                filtered_df = df1[df1['工作单位'].isin(filtered_values)]
                name_data = filtered_df['名称'].dropna().tolist()
                zichanItem['员工'] = name_data if name_data else []

                # 处理银行流水数据
                if check_file_type == "2":
                    bank_file_path = dir_path / yhFile
                    print(f"尝试访问的文件路径: {bank_file_path}")
                    if not bank_file_path.exists():
                        return jsonify({
                            'success': False,
                            'message': f'自定义文件不存在'
                        })
                    df2 = analyzer.read_excel_files(dir_path, yhFile, [], 0)
                else:
                    bank_file_path = analyzer.source_dir / item / BANK_DATA_FILE
                    if not bank_file_path.exists():
                        return jsonify({
                            'success': False,
                            'message': f'{item}的银行数据流水.xlsx文件不存在'
                        })
                    df2 = analyzer.read_excel_files(analyzer.source_dir / item, BANK_DATA_FILE, [], 0)

                if df2.empty:
                    continue

                other_name = filtered_values + name_data
                if other_name and item in selected_relatives:
                    filtered_bank_data = df2[
                        df2['名称'].isin(selected_relatives[item]) &
                        df2['交易对方名称'].isin(other_name) &
                        (df2['借贷标志'] == '出')
                    ]
                else:
                   filtered_bank_data = df2[
                        df2['交易对方名称'].isin(other_name) &
                        (df2['借贷标志'] == '出')
                    ] 
                total_amount = filtered_bank_data['交易金额'].sum()
                zichanItem['交易金额总和'] = float(total_amount) if not pd.isna(total_amount) else 0
                # 将关联的银行流水数据转换为字典列表后插入到统计结果中
                if not filtered_bank_data.empty:
                    # 根据名称统计交易金额总和
                    amount_by_name = filtered_bank_data.groupby('名称')['交易金额'].sum().reset_index()
                    # 将统计结果转换为字典列表
                    amount_stats = amount_by_name.to_dict('records')
                    # 检查 qishu_df 是否存在且不为空
                    if not qishu_df.empty and '关系' in qishu_df.columns:
                        # 用amount_stats中的名称匹配qishu_df中对应的关系数值
                        name_relation_map = dict(zip(qishu_df['名称'], qishu_df['关系']))
                        for stat in amount_stats:
                            if '名称' in stat and stat['名称'] in name_relation_map:
                                stat['关系'] = name_relation_map[stat['名称']]

                    # 插入到统计结果中
                    zichanItem['按名称统计交易金额'] = amount_stats

                    # 格式化所有datetime列
                    for col in filtered_bank_data.select_dtypes(include=['datetime64']).columns:
                        filtered_bank_data[col] = filtered_bank_data[col].dt.strftime('%Y-%m-%d %H:%M:%S')
                    
                    # 处理数据类型转换
                    def process_bank_record(record):
                        for key, value in record.items():
                            if pd.isna(value):
                                record[key] = None
                            elif isinstance(value, (pd.Timestamp, pd.DatetimeTZDtype)):
                                record[key] = value.isoformat()
                            elif isinstance(value, (np.int64, np.float64)):
                                record[key] = float(value) if isinstance(value, np.float64) else int(value)
                        return record
                    
                    bank_records = filtered_bank_data.to_dict('records')
                    zichanItem['关联银行流水数据'] = [process_bank_record(record) for record in bank_records]
                else:
                    zichanItem['关联银行流水数据'] = []
                    # 删除下面这行，避免将 DataFrame 对象赋值给 JSON 序列化字段
                    # zichanItem['关联银行流水数据'] = filtered_bank_data
                # else:
                #     zichanItem['交易金额总和'] = 0
                #     zichanItem['关联银行流水数据'] = []

            try:
                zichanItem['资产金额'] = float(zichanItem['资产金额'])
            except (ValueError, TypeError):
                zichanItem['资产金额'] = 0

            zichanItem['资产预警'] = ''
            try:
                chazhi_float = float(chazhi) if chazhi else 0
                chazhiRate_float = float(chazhiRate) if chazhiRate else 0
            except (ValueError, TypeError):
                chazhi_float = 0
                chazhiRate_float = 0

            if zichanItem['交易金额总和'] == 0:
                zichanItem['资产差额'] = 0
                zichanItem['资产差额百分比'] = 0
            else:
                zichanItem['资产差额'] = abs(zichanItem['资产金额'] - zichanItem['交易金额总和'])
                zichanItem['资产差额百分比'] = abs(round(zichanItem['资产差额'] / zichanItem['资产金额'], 4) * 100)

                if zichanItem['资产差额'] >= chazhi_float or zichanItem['资产差额百分比'] >= chazhiRate_float:
                    zichanItem['资产预警'] = (
                        f"{zichanItem.get('资产信息', '')}存在异常，"
                        f"资产金额为{zichanItem['资产金额']}元，"
                        f"发生交易金额为{zichanItem['交易金额总和']}元，"
                        f"差值{zichanItem['资产差额百分比']}%"
                    )

            zichanItem['资产差额百分比'] = f"{zichanItem['资产差额百分比']}%"

        # 根据请求返回 Excel 文件或 JSON 数据
        if is_excel == "1":
            df = pd.DataFrame(company)
            output = BytesIO()
            with pd.ExcelWriter(output, engine='openpyxl') as writer:
                df.to_excel(writer, index=False, sheet_name='资产数据分析')
            output.seek(0)
            return send_file(
                output,
                mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                as_attachment=True,
                download_name='资产数据分析.xlsx'
            )
        elif is_excel == "2":
            # 提取所有关联银行流水数据
            all_bank_data = []
            for item in company:
                if '关联银行流水数据' in item and item['关联银行流水数据']:
                    all_bank_data.extend(item['关联银行流水数据'])
            
            if all_bank_data:
                df = pd.DataFrame(all_bank_data)
                output = BytesIO()
                with pd.ExcelWriter(output, engine='openpyxl') as writer:
                    df.to_excel(writer, index=False, sheet_name='关联银行流水数据')
                output.seek(0)
                return send_file(
                    output,
                    mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                    as_attachment=True,
                    download_name='关联银行流水数据.xlsx'
                )
            else:
                return jsonify({
                    'success': False,
                    'message': '没有关联的银行流水数据'
                })
        else:
            return jsonify({
                'success': True,
                'data': company
            })

    except Exception as e:
        print(f"[DEBUG] 发生错误: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({
            'success': False,
            'message': f'获取资产数据时出错: {str(e)}'
        })
@asset_situation_bp.route('/get-propety', methods=['GET'])
def get_user_propety():
    """
        获取用户雌蝉的资产开发、服务、销售商、其他
    """
    # 被审查人
    subdirectories = request.args.getlist('folders')
    # 审查亲属
    selected_relatives = request.args.getlist('relatives')
    data = []
    try:
        # 初始化资源路径
        analyzer.get_source_dir('source')
        # 获取资源路径下的所有子目录名称
        if not subdirectories:
            subdirectories = analyzer.get_subdirectories('source')

        filename = '资产关系.xlsx'
        for item in subdirectories:
            file_path = analyzer.source_dir / item / filename
            if not file_path.exists():
                return jsonify({
                    'success': False,
                    'message': f'{item}的资产关系.xlsx文件不存在'
                })
            
            df = analyzer.read_excel_files(analyzer.source_dir / item, filename, [], 1)
            # 筛选所属人员在selected_relatives队列的数据，如果selected_relatives为空则不筛选
            if selected_relatives:
                df = df[df['所属人员'].isin(selected_relatives)]
            
            if df.empty:
                continue
            
            # 验证表格中是否有开发商、服务商、销售商、其他字段
            if '开发商' in df.columns:
                developer = [{
                    'value': x.tolist() if isinstance(x, np.ndarray) else x,
                    'owner': owner
                } for x, owner in zip(df['开发商'].dropna(), df.loc[df['开发商'].dropna().index, '所属人员'])]
            else :
                developer = []
            if '服务商' in df.columns:
                service = [{
                    'value': x.tolist() if isinstance(x, np.ndarray) else x,
                    'owner': owner
                } for x, owner in zip(df['服务商'].dropna(), df.loc[df['服务商'].dropna().index, '所属人员'])]
            else :
                service = []
            if '销售商' in df.columns:  
                seller = [{
                    'value': x.tolist() if isinstance(x, np.ndarray) else x,
                    'owner': owner
                } for x, owner in zip(df['销售商'].dropna(), df.loc[df['销售商'].dropna().index, '所属人员'])]
            else :
                seller = []
            if '其他' in df.columns:
                other = [{
                    'value': x.tolist() if isinstance(x, np.ndarray) else x,
                    'owner': owner
                } for x, owner in zip(df['其他'].dropna(), df.loc[df['其他'].dropna().index, '所属人员'])]
            else :
                other = []
            
            data.append(pd.DataFrame([{
                'name': item,
                'developer': developer,
                'service': service,
                'seller': seller,
                'other': other
            }]))
       
        if not data:
            return jsonify({
                'success': False,
                'message': '没有数据'
            })
        # 合并所有数据
        combined_df = pd.concat(data, ignore_index=True)
        
        # 获取所有列名
        columns = combined_df.columns.tolist()

        # 获取选择的字段
        selected_fields = request.args.getlist('fields')
        if selected_fields:
            # 确保所有请求的字段都存在
            valid_fields = [field for field in selected_fields if field in columns]
            if valid_fields:
                combined_df = combined_df[valid_fields]
            else:
                return jsonify({'error': '没有找到有效的字段'})
    
        # 转换数据为字典列表
        data = combined_df.to_dict('records')
        
        return jsonify({
            'success': True,
            'data': data,
            'columns': columns,
            'total_records': len(data)
        })

    except Exception as e:
        print(f"获取数据出错: {str(e)}")  # 打印错误信息
        return jsonify({
            'success': False,
            'error': f'生成分析结果时出错: {str(e)}'
        })

@asset_situation_bp.route('/get-staff', methods=['GET'])
def get_staff():
    """
    获取公司对应特定关系人表中的员工
    """
    # 被审查人
    subdirectories = request.args.getlist('folders')
    company = request.args.getlist('company')
    relative = request.args.getlist('relative')
     # 初始化资源路径
    analyzer.get_source_dir('source')
    # 获取资源路径下的所有子目录名称
    if not subdirectories:
        subdirectories = analyzer.get_subdirectories('source')

    filename = '特定关系人.xlsx'
    staff = []
    data = []
    companyData = []
    # 获取特定关系人
    for item in subdirectories:
        file_path = analyzer.source_dir / item / filename
        if not file_path.exists():
            return jsonify({
                'success': False,
                'message': f'{item}的特定关系人系.xlsx文件不存在'
            })
        
        df = analyzer.read_excel_files(analyzer.source_dir / item, filename)
        # 筛选工作单位在company队列的数据
        filtered_df = df[df['工作单位'].isin(company)]
        if not filtered_df.empty:
            # 按工作单位分组，对名称去重去空
            grouped = filtered_df.groupby('工作单位')['名称'].apply(lambda x: sorted(x.dropna().unique())).reset_index()
            # 转换为目标格式
            for _, row in grouped.iterrows():
                if row['名称']:  # 确保姓名列表不为空
                    companyData.append({
                        'company': row['工作单位'],
                        'staff': row['名称']
                    })

    print(f'生成数据：{companyData}')

    # for item in subdirectories:
    #     file_path = analyzer.source_dir / item / '银行流水数据库.xlsx'
    #     if not file_path.exists():
    #         return jsonify({
    #             'success': False,
    #             'message': f'{item}的银行流水数据库.xlsx文件不存在'
    #         })


    if not data:
        return jsonify({
            'success': False,
            'message': '没有数据'
        })

